﻿using System;
using System.Data;
using System.Configuration;
using System.Data.OleDb;
/// <summary>
/// Summary description for ExcelReader
/// </summary>
namespace eTMS.Commons
{
    public class ExcelReader
    {
        public ExcelReader()
        {
        }
        public static DataSet ReadExcellFile(string FileName, string SheetName)
        {
            string strConn = "";
            if (System.IO.Path.GetExtension(FileName).ToLower() == ".xlsx")
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
            }
            else
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                       "Data Source=" + FileName +
                       ";Extended Properties=Excel 8.0;";
            }
            OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [" + SheetName + "$]", strConn);
            DataSet myDataSet = new DataSet();
            try
            {
                myCommand.Fill(myDataSet, "TableName");
            }
            catch
            {
                myDataSet.Tables.Add(new DataTable());
            }
            return myDataSet;
        }
        public static DataTable ReadExcellSheet(string FileName)
        {
            string strConn = "";
            if (System.IO.Path.GetExtension(FileName).ToLower() == ".xlsx")
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
            }
            else
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                       "Data Source=" + FileName +
                       ";Extended Properties=Excel 8.0;";
            }
            OleDbConnection myCommand = new OleDbConnection(strConn);
            myCommand.Open();
            DataTable tbl = myCommand.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            myCommand.Close();
            foreach (DataRow r in tbl.Rows)
            {
                r["TABLE_NAME"] = r["TABLE_NAME"].ToString().TrimEnd('_').TrimEnd('$');
            }
            return tbl;
        }
        public static DataTable ReadExcellColumn(string FileName, string SheetName)
        {
            string strConn = "";
            if (System.IO.Path.GetExtension(FileName).ToLower() == ".xlsx")
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
            }
            else
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                       "Data Source=" + FileName +
                       ";Extended Properties=Excel 8.0;";
            }
            OleDbConnection myCommand = new OleDbConnection(strConn);
            try
            {
                myCommand.Open();
                string stSheet="" + SheetName + "$";
                if(SheetName.IndexOf(" ")>=0)
                    stSheet="[" + SheetName + "$]";
                DataTable tbl = myCommand.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, stSheet, null });
                myCommand.Close();
                return tbl;
            }
            catch
            {

            }
            return new DataTable();
        }
    }
}
